package com.yonyou.crm.tpub.excel.ctrl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.support.WebApplicationContextUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.yonyou.crm.common.exception.CrmBusinessException;
import com.yonyou.crm.common.exception.CrmMessageException;
import com.yonyou.crm.common.page.entity.Page;
import com.yonyou.crm.common.rest.constant.PCCodeConstant;
import com.yonyou.crm.common.rest.util.RequestUtil;
import com.yonyou.crm.common.rest.util.ResponseUtil;
import com.yonyou.crm.cum.customer.ctrl.OSSClientUtil;
import com.yonyou.crm.cum.customer.entity.CustomerVO;
import com.yonyou.crm.cum.customer.rmitf.ICustomerRmService;
import com.yonyou.crm.cum.customer.rmitf.IGroupCustomerRmService;
import com.yonyou.crm.tpub.attachment.entity.AttachmentConstant;
import com.yonyou.crm.tpub.excel.entity.ExcelDataRuleVO;
import com.yonyou.crm.tpub.excel.entity.ExcelDataTypeEnum;
import com.yonyou.crm.tpub.excel.entity.ExcelExportModeEnum;
import com.yonyou.crm.tpub.excel.entity.ExcelFieldVO;
import com.yonyou.crm.tpub.excel.entity.ExcelRowErrorVO;
import com.yonyou.crm.tpub.excel.entity.ExcelSheetErrorVO;
import com.yonyou.crm.tpub.excel.entity.ExcelTemplateConstant;
import com.yonyou.crm.tpub.excel.entity.ExcelTemplateVO;
import com.yonyou.crm.tpub.excel.rmitf.IExcelRmService;

@RestController
@RequestMapping(value = "/tpub/excels")
public class ExcelCtrl {
	
	private final static Logger logger = LoggerFactory.getLogger(ExcelCtrl.class);
	
	@Autowired
	private IExcelRmService service;
	
	/**
	 * 导入模板
	 * @param request
	 * @param response
	 * @return
	 * @throws CrmMessageException 
	 */
	@RequestMapping(value = "{type}/templates/import", method = RequestMethod.GET)
	public void importTemplate(@PathVariable("type") Integer type, HttpServletRequest request, HttpServletResponse response) throws CrmMessageException {

		if (type == null) {
			throw new CrmMessageException(PCCodeConstant.ServerError ,"类型不能为空");
		}

		//根据租户、模板类型查询数据库获得模板
		ExcelTemplateVO etVO = getImportTemplateVO(type);

		//导出Excel
		exportExcel(response, getTplWorkBook(etVO), etVO.getEntityName() + "导入模板.xls");
	}
	
	/**
	 * 导入Excel
	 * @param request
	 * @param response
	 * @return
	 * @throws  
	 */
	@RequestMapping(value = "{type}/import",method = RequestMethod.POST)
	public Object importExcel(@PathVariable("type") Integer type, HttpServletRequest request, HttpServletResponse response) throws CrmMessageException {
		
		if (type == null) {
			throw new CrmMessageException(PCCodeConstant.ServerError ,"类型不能为空");
		}
		
		ExcelTemplateVO etVO = getImportTemplateVO(type);

		List<ExcelSheetErrorVO> seVOList = importExcel(request, etVO);
		String msg = getMsg(seVOList);
		if (seVOList.size() > 0) {
			
			//生成错误工作薄
			Workbook wookbook = getErrorWorkBook(etVO, seVOList);

			ByteArrayOutputStream os = new ByteArrayOutputStream();
			try {
				wookbook.write(os);
			} catch (IOException e) {
				throw new CrmMessageException("生成错误列表失败");
			}

			byte[] content = os.toByteArray();
			InputStream is = new ByteArrayInputStream(content);

			String entityName = etVO.getEntityName();
			if (StringUtils.isBlank(entityName)) {
				throw new CrmMessageException("模板实体名称不能为空");
			}

			//拼接文件名
			String fileName = entityName + "导入数据失败信息";
			//上传服务器文件名称不允许重复
			String serverFileName = fileName + "_" + new Date().getTime() + ".xls";

			//上传错误文档
			OSSClientUtil ossClient = new OSSClientUtil();
			ossClient.uploadFile2OSS(is, AttachmentConstant.ERROR_EXCEL_DIR + "/" + serverFileName);
			//获得下载URL
			String errorURL = ossClient.getUrl(AttachmentConstant.ERROR_EXCEL_DIR + "/" + serverFileName);
			
			Map<String, Object> dataMap = new HashMap<String, Object>();
			dataMap.put("errorURL", errorURL);
			dataMap.put("fileName", fileName + ".xls");
			return ResponseUtil.getObject(dataMap, msg + "，查看失败原因请下载失败信息。");
		}

		return ResponseUtil.getObject(msg);
	}
	
	/**
	 * 导出Excel
	 * @param type
	 * @param request
	 * @param response
	 * @return
	 * @throws CrmMessageException
	 */
	@RequestMapping(value = "{type}/export",method = RequestMethod.GET)
	public void exportExcel(@PathVariable("type") Integer type, HttpServletRequest request, HttpServletResponse response) throws CrmMessageException {
		
		if (type == null) {
			throw new CrmMessageException(PCCodeConstant.ServerError ,"类型不能为空");
		}

		try {
			
			JSONObject paramObj = RequestUtil.getParamObject(request);
			Integer mode = getMode(paramObj);
			if (mode == null) {
				throw new CrmMessageException(PCCodeConstant.ServerError ,"模式不能为空");
			}
			
			Page<?> page = null;
			if (mode.intValue() == ExcelExportModeEnum.CURRENT.getValue()) {
				page = getRequestPage(paramObj);
			}
			
			ExcelTemplateVO etVO = getExportTemplateVO(type);
			
			long sSt = new Date().getTime();
			//调用查询服务
			ApplicationContext context = WebApplicationContextUtils.getRequiredWebApplicationContext(request.getServletContext());
			Object selectService = context.getBean(etVO.getService());
			Method selectMethod = selectService.getClass().getMethod(etVO.getMethod(), new Class[]{Page.class, Map.class});
			Page<?> dataPage = (Page<?>) selectMethod.invoke(selectService, page, getSearchMap(paramObj));
			long sEt = new Date().getTime();
			logger.debug("查询数据时间：" + ((sEt - sSt) / 1000) + "秒");
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = getDataSheet(etVO, workbook);
			
			List<?> dataList = dataPage.getData();
			if (dataList != null && dataList.size() > 0) {
				
				long dSt = new Date().getTime();
				//字段列表
				List<ExcelFieldVO> fieldList = etVO.getFieldList();
				//每一行
				for (int rowIndex=0; rowIndex<dataList.size(); rowIndex++) {
					
					Object entityObj = dataList.get(rowIndex);
					HSSFRow row = sheet.createRow(rowIndex + 1);
					//每一列
					for (int cellIndex=0; cellIndex<fieldList.size(); cellIndex++) {
						
						ExcelFieldVO fieldVO = fieldList.get(cellIndex);
						Field entityField = etVO.getEntityClass().getDeclaredField(fieldVO.getFieldName());
						entityField.setAccessible(true);
						
						HSSFCell cell = row.createCell(cellIndex);
						Object valueObj = entityField.get(entityObj);
						if (valueObj != null) {
							cell.setCellValue(valueObj.toString());
						}
					}

				}
				long dEt = new Date().getTime();
				logger.debug("填充数据时间：" + ((dEt - dSt) / 1000) + "秒");
			}
			
			//导出Excel
			exportExcel(response, workbook, etVO.getEntityName() + "数据.xls");
			
		} catch (Exception e) {
			
			e.printStackTrace();
			throw new CrmMessageException("导出Excel数据失败，原因：" + e.getMessage());
		}
	}
	
	/**
	 * 获得成功提示信息
	 * @param seVOList
	 * @return
	 */
	private String getMsg(List<ExcelSheetErrorVO> seVOList) {
		
		if (seVOList.size() == 0) {
			return "数据已全部导入成功";
		}
		
		int totalCount = 0;
		int failCount = 0;
		for (ExcelSheetErrorVO seVO : seVOList) {
			totalCount += seVO.getRowCount();
			failCount += seVO.getRowErrorList().size();
		}

		if (failCount == 0) {
			return "数据已全部导入成功";
		}
		
		return "成功" + (totalCount - failCount) + "条，失败" + failCount + "条";
	}
	
	/**
	 * 导出模式，1当前页，2所有
	 * @param paramObj
	 * @return
	 */
	private Integer getMode(JSONObject paramObj) {
		Integer mode = null;
		if (paramObj != null) {
			mode = (Integer)paramObj.get("mode");
		}
		
		return mode;
	}
	
	/**
	 * 获得搜索条件map
	 * @param paramObj
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private Map<String, Object> getSearchMap(JSONObject paramObj) {
		
		Map<String, Object> searchMap = null;
		if (paramObj != null) {
			searchMap = (Map<String, Object>)paramObj.get("searchMap");
		}

		if (searchMap == null) {
			searchMap = new HashMap<String, Object>();
		}
		
		//查询方案
		Map<String, Object> searchPlanMap = (Map<String, Object>) paramObj.get("searchPlanMap");
		if (searchPlanMap != null && searchPlanMap.size() > 0) {
			searchMap.put("id", searchPlanMap.get("id"));
			searchMap.put("defClass", searchPlanMap.get("defClass"));
		}
		
		return searchMap;
	}
	
	/**
	 * 获得请求分页信息
	 * @param paramObj
	 * @return
	 */
	private Page<?> getRequestPage(JSONObject paramObj) {
		
		Integer page = null;
		Integer pageSize = null;
		if (paramObj != null) {
			page = (Integer)paramObj.get("page");
			pageSize = (Integer)paramObj.get("pageSize");
		}
		
		page = page == null ? 1 : page;
		pageSize = pageSize == null ? 10 : pageSize;
		
		return new Page<>(page, pageSize);
	}
	
	/**
	 * 根据模板创建工作薄
	 * @param etVO
	 * @return
	 */
	private Workbook getTplWorkBook(ExcelTemplateVO etVO) {
		
		//工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();

		//说明工作表
		getDescSheet(etVO, workbook);
		
		//数据工作表
		getDataSheet(etVO, workbook);
		
		return workbook;
	}
	
	/**
	 * 获得说明工作表
	 * @param workbook
	 * @return
	 */
	private HSSFSheet getDescSheet(ExcelTemplateVO etVO, HSSFWorkbook workbook) {
		
		HSSFSheet sheet = workbook.createSheet("说明");
		
		//水平垂直居中
		HSSFCellStyle centerStyle = getCenterStyle(workbook);
		
		//水平居中
		HSSFCellStyle vCenterStyle = workbook.createCellStyle();
		vCenterStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		vCenterStyle.setFont(getDefaultFont(workbook));
		
		//水平居中红色字体
		HSSFCellStyle rCenterStyle = workbook.createCellStyle();
		rCenterStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		HSSFFont rfont = getDefaultFont(workbook);
		rfont.setColor(HSSFColor.RED.index);
		rCenterStyle.setFont(rfont);

		HSSFRow headerRow = sheet.createRow(0);
		headerRow.setHeight((short)500);
		HSSFCell columnCell = headerRow.createCell(0);
		columnCell.setCellValue("列名");
		columnCell.setCellStyle(centerStyle);
		
		HSSFCell ruleCell = headerRow.createCell(1);
		ruleCell.setCellValue("规则");
		ruleCell.setCellStyle(centerStyle);
		
		//列宽
		sheet.setColumnWidth(0, 3 * 1500);
		sheet.setColumnWidth(1, 3 * 1000);
		sheet.setColumnWidth(2, 3 * 3000);
		//合并列
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
		
		List<ExcelFieldVO> fieldList = etVO.getFieldList();
		for (int i=0; i<fieldList.size() * 2; i=i+2) {
			
			int rIndex = i + 1;
			int fIndex = i + 2;
			
			ExcelFieldVO fieldVO = fieldList.get(i / 2);
			
			HSSFRow rRow = sheet.createRow(rIndex);
			rRow.setHeight((short)400);
			HSSFCell cCell  = rRow.createCell(0);
			cCell.setCellValue(fieldVO.getCellName());
			cCell.setCellStyle(vCenterStyle);
			
			HSSFCell rCell  = rRow.createCell(1);
			rCell.setCellValue("必输项");
			rCell.setCellStyle(vCenterStyle);
			HSSFCell rvCell  = rRow.createCell(2);
			if (fieldVO.isRequired()) {
				rvCell.setCellValue("是");
				rvCell.setCellStyle(rCenterStyle);
			}
			else {
				rvCell.setCellValue("否");
				rvCell.setCellStyle(vCenterStyle);
			}
				
			
			HSSFRow fRow = sheet.createRow(fIndex);
			fRow.setHeight((short)400);
			HSSFCell fCell  = fRow.createCell(1);
			fCell.setCellValue("格式");
			fCell.setCellStyle(vCenterStyle);
			HSSFCell fvCell  = fRow.createCell(2);
			ExcelDataRuleVO ruleVO = fieldVO.getDataRule();
			if (ruleVO != null) {
				fvCell.setCellValue(ruleVO.getFormat());
			}

			//合并行
			sheet.addMergedRegion(new CellRangeAddress(rIndex, fIndex, 0, 0));
		}

		return sheet;
	}
	
	/**
	 * 获得数据工作表
	 * @param etVO
	 * @param workbook
	 * @return
	 */
	private HSSFSheet getDataSheet(ExcelTemplateVO etVO, HSSFWorkbook workbook) {
		
		HSSFSheet sheet = workbook.createSheet("数据");
		sheet.setDefaultColumnWidth(500);
		sheet.setActive(true);
		//取消受保护视图
		
		List<ExcelFieldVO> fieldList = etVO.getFieldList();
		
		//表头
		HSSFRow headerRow = sheet.createRow(0);

		//表头单元格样式
		HSSFCellStyle headerStyle = getCenterStyle(workbook);
		//数据单元格样式
		HSSFCellStyle dataStyle = workbook.createCellStyle();
		HSSFDataFormat dataFormat = workbook.createDataFormat();
		//文本格式
		dataStyle.setDataFormat(dataFormat.getFormat("@"));
		
		for (int i=0; i<fieldList.size(); i++) {
			
			ExcelFieldVO fieldVO = fieldList.get(i);

			//创建单元格
			HSSFCell headerCell = headerRow.createCell(i);
			headerCell.setCellStyle(headerStyle);
			headerCell.setCellValue(fieldVO.getCellName());
			
			sheet.setDefaultColumnStyle(i, dataStyle);
			
			sheet.setColumnWidth(i, fieldList.size() * 200);
		}
		
		return sheet;
	}
	
	/**
	 * 生成错误工作薄
	 * @param etVO
	 * @param seVOList
	 * @return
	 */
	private Workbook getErrorWorkBook(ExcelTemplateVO etVO, List<ExcelSheetErrorVO> seVOList) {
		
		//工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = getErrorSheet(workbook);
		sheet.setColumnWidth(0, 3 * 500);
		sheet.setColumnWidth(1, 3 * 1500);
		sheet.setColumnWidth(2, 3 * 7500);
		
		for (ExcelSheetErrorVO seVO : seVOList) {			
			
			List<ExcelRowErrorVO> reVOList = seVO.getRowErrorList();
			for (int i=0; i<reVOList.size(); i++) {
				
				HSSFRow row = sheet.createRow(i+1);
				ExcelRowErrorVO reVO = reVOList.get(i);

				HSSFCell rowCell = row.createCell(0);
				rowCell.setCellValue(reVO.getRow() + 1);
				HSSFCell columnCell = row.createCell(1);
				columnCell.setCellValue(reVO.getColumnName());
				HSSFCell reasonCell = row.createCell(2);
				reasonCell.setCellValue(reVO.getReason());
			}
			
		}
		
		return workbook;
	}
	
	/**
	 * 获得错误工作表
	 * @param workbook
	 * @return
	 */
	private HSSFSheet getErrorSheet(HSSFWorkbook workbook) {
		
		HSSFCellStyle centerStyle = getCenterStyle(workbook);
		
		HSSFSheet sheet = workbook.createSheet("失败原因");
		HSSFRow headerRow = sheet.createRow(0);
		HSSFCell rowCell = headerRow.createCell(0);
		rowCell.setCellStyle(centerStyle);
		rowCell.setCellValue("行号");
		HSSFCell columnCell = headerRow.createCell(1);
		columnCell.setCellStyle(centerStyle);
		columnCell.setCellValue("列名");
		HSSFCell reasonCell = headerRow.createCell(2);
		reasonCell.setCellStyle(centerStyle);
		reasonCell.setCellValue("失败原因");
		
		return sheet;
	}
	
	/**
	 * 导出Excel
	 * @param response
	 * @param workbook
	 * @param fileName
	 * @throws CrmMessageException
	 */
	private void exportExcel(HttpServletResponse response, Workbook workbook, String fileName) throws CrmMessageException {
		OutputStream os = null;
		try {
			os = response.getOutputStream();
			response.reset();
			response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
			response.setContentType("application/msexcel");
			workbook.write(os);
		} catch (Exception e) {
			throw new CrmMessageException(PCCodeConstant.ServerError, "生成Excel失败。");
		} finally {
			try {
				os.flush();
				os.close();
			} catch (Exception e) {
				throw new CrmMessageException(PCCodeConstant.ServerError, "输出流操作失败。");
			}
		}
	}

	/**
	 * 根据模板导入Excel
	 * @param request
	 * @param etVO
	 * @return
	 * @throws CrmMessageException
	 */
	private List<ExcelSheetErrorVO> importExcel(HttpServletRequest request, ExcelTemplateVO etVO) throws CrmMessageException {
		
		ApplicationContext context = WebApplicationContextUtils.getRequiredWebApplicationContext(request.getServletContext());
		
		//工作表错误信息列表
		List<ExcelSheetErrorVO> sheetEList = new ArrayList<ExcelSheetErrorVO>();
		//无效值
		Map<String, Set<String>> invalidValueMap = new HashMap<String, Set<String>>();

		try {
			//解析Excel
			MultipartFile mFile = ((MultipartHttpServletRequest)request).getFile("file");

			//模板字段列表
			List<ExcelFieldVO> fieldList = etVO.getFieldList();
			//读取工作薄
			Workbook workbook = getWorkbook(mFile.getInputStream(), mFile.getOriginalFilename());
			
			if (workbook.getNumberOfSheets() < 2) {
				List<ExcelRowErrorVO> rowEList = new ArrayList<ExcelRowErrorVO>();
				rowEList.add(new ExcelRowErrorVO(0, "Excel与模板不匹配，第一个工作表为说明，第二个工作表为数据。"));
				sheetEList.add(new ExcelSheetErrorVO(1, "", rowEList, 1));
				return sheetEList;
			}
			
			int sheetTotalCount = workbook.getNumberOfSheets();
			//暂时支持单数据sheet
			sheetTotalCount = 2;
			
			//读取工作表
			//索引从1开始：第一个工作表为说明
			for (int sheetIndex=1; sheetIndex<sheetTotalCount; sheetIndex++) {
				
				Sheet sheet = workbook.getSheetAt(sheetIndex);
				if (isEmptySheet(sheet)) {
					continue;
				}

				//工作表错误信息
				List<ExcelRowErrorVO> rowEList = new ArrayList<ExcelRowErrorVO>();
				//实体列表
				List<Map<String, Object>> entityList = new ArrayList<Map<String,Object>>();
				
				long eSt = new Date().getTime();
				int rowTotalCount = sheet.getLastRowNum();
				//读取行
				rowOuter://行循环标志
				for (int rowIndex=0; rowIndex<=rowTotalCount; rowIndex++) {

					Row row = sheet.getRow(rowIndex);
					if (isEmptyRow(row)) {
						continue;
					}

					int cellTotalCount = row.getLastCellNum();
					//列头
					if (rowIndex == 0) {
						//与模板列数不匹配
						if (cellTotalCount != fieldList.size()) {
							rowEList.add(new ExcelRowErrorVO(rowIndex, "数据列与模板列不匹配"));
							rowTotalCount = 1;
							break;
						}
						//跳过列头
						continue;
					}

					Object entityObj = null;
					try {
						entityObj = etVO.getEntityClass().newInstance();
					} catch (Exception e) {
						rowEList.add(new ExcelRowErrorVO(rowIndex, e.getMessage()));
						e.printStackTrace();
						continue;
					}

					//读取单元格
					for (int cellIndex=0; cellIndex<cellTotalCount; cellIndex++) {

						ExcelFieldVO fieldVO = fieldList.get(cellIndex);
						
						Cell cell = row.getCell(cellIndex);
						if (cell == null) {
							continue;
						}
						
						//必输项校验
						if (fieldVO.isRequired() && StringUtils.isBlank(cell.getStringCellValue())) {
							rowEList.add(new ExcelRowErrorVO(rowIndex, cellIndex, fieldVO.getCellName(), fieldVO.getCellName() + "为必输项，值不能为空"));
							continue rowOuter;
						}
						
						String cellValue = "";
						try {
							
							cellValue = getCellValue(cell, fieldVO, invalidValueMap);
							//自定义值处理器
							cellValue = handleValue(context, etVO, cellValue, fieldVO);
							//实体字段赋值
							setFieldValue(etVO.getEntityClass(), fieldVO, entityObj, cellValue);
							
						} catch (InvocationTargetException e) {
							
							rowEList.add(new ExcelRowErrorVO(rowIndex, cellIndex, fieldVO.getCellName(), e.getTargetException().getMessage()));
							e.printStackTrace();
						} catch (Exception e) {
							
							rowEList.add(new ExcelRowErrorVO(rowIndex, cellIndex, fieldVO.getCellName(), e.getMessage()));
							e.printStackTrace();
							continue rowOuter;
						}
					}
					
					//实体列表
					Map<String ,Object> entityMap = new HashMap<String, Object>();
					entityMap.put(String.valueOf(rowIndex), entityObj);
					entityList.add(entityMap);
				}
				long eEt = new Date().getTime();
				logger.debug("填充实体列表用时：" + ((eSt - eEt) / 1000) + "秒");
				
				long dbSt = new Date().getTime();
				//新增数据
				for (Map<String ,Object> entityMap : entityList) {
					for (Map.Entry<String, Object> entry : entityMap.entrySet()) {
						insertEntity(context, etVO, entry.getValue(), rowEList, Integer.parseInt(entry.getKey()));
					}
				}
				long dbEt = new Date().getTime();
				logger.debug("插入数据用时：" + ((dbEt - dbSt) / 1000) + "秒");
				
				//错误信息汇总
				if (rowEList.size() > 0) {
					sheetEList.add(new ExcelSheetErrorVO(sheetIndex, sheet.getSheetName(), rowEList, rowTotalCount));
				}
				
			}
			
		} catch (Exception e) {
			throw new CrmMessageException("读取Excel出错");
		}
		
		return sheetEList;
	}
	
	/**
	 * 为实体字段赋值
	 * @param entityClass
	 * @param fieldVO
	 * @param entityObj
	 * @param cellValue
	 * @throws Exception
	 */
	private void setFieldValue(Class<?> entityClass, ExcelFieldVO fieldVO, Object entityObj, String cellValue) throws Exception {
		
		Field entityField = entityClass.getDeclaredField(fieldVO.getFieldName());
		entityField.setAccessible(true);

		//根据字段类型转换
		Class<?> typeClass = fieldVO.getFieldType();
		if (typeClass != null && cellValue != null) {
			entityField.set(entityObj, typeClass.getDeclaredConstructor(String.class).newInstance(cellValue));
			return;
		}
		
		//根据数据类型转换
		if (StringUtils.isNotBlank(cellValue) && ExcelDataTypeEnum.REF.getValue() == fieldVO.getDataType()) {
			entityField.set(entityObj, new Long(cellValue));
		}
		else if (StringUtils.isNotBlank(cellValue) && ExcelDataTypeEnum.ENUM.getValue() == fieldVO.getDataType() || ExcelDataTypeEnum.DOC.getValue() == fieldVO.getDataType()) {
			entityField.set(entityObj, new Integer(cellValue));
		}
		else if (ExcelDataTypeEnum.NUMERIC.getValue() == fieldVO.getDataType()) {
			
		}
		else if (ExcelDataTypeEnum.DATE.getValue() == fieldVO.getDataType()) {

		}
		else {
			entityField.set(entityObj, cellValue);
		}
	}
	
	/**
	 * 调用服务新增数据
	 * @param request
	 * @param etVO
	 * @param entityObj
	 * @param rowEList
	 * @param rowIndex
	 */
	private void insertEntity(ApplicationContext context, ExcelTemplateVO etVO, Object entityObj, List<ExcelRowErrorVO> rowEList, int rowIndex) {
		
		//调用新增服务
		Object insertService = context.getBean(etVO.getService());
		Method insertMethod = null;
		try {
			
			insertMethod = insertService.getClass().getMethod(etVO.getMethod(), new Class[]{etVO.getEntityClass()});
			insertMethod.invoke(insertService, entityObj);
		} catch (InvocationTargetException e) {
			
			//业务异常拦截CrmBusinessException
			rowEList.add(new ExcelRowErrorVO(rowIndex, e.getTargetException().getMessage()));
			e.printStackTrace();
		}catch (Exception e) {
			
			rowEList.add(new ExcelRowErrorVO(rowIndex, e.getMessage()));
			e.printStackTrace();
		}
	}

	/**
	 * 自定义值处理器
	 * @param context
	 * @param etVO
	 * @return
	 * @throws Throwable 
	 */
	private String handleValue(ApplicationContext context, ExcelTemplateVO etVO, String cellValue, ExcelFieldVO fieldVO) throws Exception {
		Class<?> handlerClass = etVO.getValueHandler();
		if (handlerClass != null) {
			Object valueHandler = context.getBean(handlerClass);
			Method handleMethod = valueHandler.getClass().getMethod("handle", new Class[]{Cell.class, ExcelFieldVO.class});
			return (String)handleMethod.invoke(cellValue, fieldVO);
		}
		return cellValue;
	}

	/**
	 * 获取单元格值
	 * @param cell
	 * @param fieldVO
	 * @return
	 */
	private String getCellValue(Cell cell, ExcelFieldVO fieldVO, Map<String, Set<String>> invalidValueMap) {
		
		//根据数据类型，校验值						
		//数值，正负数、整数、小数
		//手机号、邮箱
		
		String value = "";
		int dataType = fieldVO.getDataType();
		ExcelDataRuleVO drVO = fieldVO.getDataRule();
		Set<String> invalidValueSet = invalidValueMap.get(fieldVO.getFieldName());
		
		if (ExcelDataTypeEnum.ENUM.getValue() == dataType || ExcelDataTypeEnum.DOC.getValue() == dataType || ExcelDataTypeEnum.REF.getValue() == dataType) {

			if (invalidValueSet != null && invalidValueSet.contains(cell.getStringCellValue()) && fieldVO.isRequired()) {
				throw new CrmBusinessException("相关档案【" + cell.getStringCellValue() + "】不存在");
			}
			else {
				//根据名称查询值
				value = service.getValueByName(dataType, drVO, cell.getStringCellValue());
				//未查询到匹配的值
				if (StringUtils.isBlank(value)) {
					
					if (invalidValueSet == null) {
						invalidValueSet = new HashSet<String>();
						invalidValueMap.put(fieldVO.getFieldName(), invalidValueSet);
					}
					//添加非法值
					invalidValueSet.add(cell.getStringCellValue());
					if (fieldVO.isRequired()) {
						throw new CrmBusinessException("相关档案【" + cell.getStringCellValue() + "】不存在");
					}
				}
			}
		}
		else {
			value = cell.getStringCellValue();
		}
	
		return value;
	}
	
	/**
	 * 获取工作薄
	 * @param is
	 * @param fileName
	 * @return
	 * @throws IOException
	 */
	private Workbook getWorkbook(InputStream is, String fileName) throws IOException {
		if (fileName.endsWith(".xls")) {
			return new HSSFWorkbook(is);
		} else if (fileName.endsWith(".xlsx")) {
			return new XSSFWorkbook(is);
		}
		return null;
	}
	
	/**
	 * 表头默认单元格样式
	 * @param workbook
	 * @return
	 */
	private HSSFCellStyle getCenterStyle(HSSFWorkbook workbook) {
		
		HSSFCellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		headerStyle.setFont(getDefaultFont(workbook));
		
		return headerStyle;
	}
	
	/**
	 * 获得默认字体
	 * @param workbook
	 * @return
	 */
	private HSSFFont getDefaultFont(HSSFWorkbook workbook) {
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 10);
		font.setFontName("宋体");
		return font;
	}
	
	/**
	 * 是否空工作表
	 * @param sheet
	 * @return
	 */
	private boolean isEmptySheet(Sheet sheet) {
		return sheet == null || (sheet.getLastRowNum() == 0 && sheet.getPhysicalNumberOfRows() == 0);
	}
	
	/**
	 * 是否空行
	 * @param row
	 * @return
	 */
	private boolean isEmptyRow(Row row) {
		return row == null || (row.getLastCellNum() == 0 && row.getPhysicalNumberOfCells() == 0);
	}
	
	//导入模板
	private ExcelTemplateVO getImportTemplateVO(Integer type) {
		
		ExcelTemplateVO etVO = new ExcelTemplateVO();

		switch (type.intValue()) {
			case ExcelTemplateConstant.Customer:
				etVO.setFieldList(getCustomerImportFieldList());
				etVO.setEntityName("公司客户");
				etVO.setEntityClass(CustomerVO.class);
				etVO.setService(ICustomerRmService.class);
				etVO.setMethod("insert");
				break;
			case ExcelTemplateConstant.GroupCustomer:
				etVO.setFieldList(getCustomerImportFieldList());
				etVO.setEntityName("集团客户");
				etVO.setEntityClass(CustomerVO.class);
				etVO.setService(IGroupCustomerRmService.class);
				etVO.setMethod("insert");
				break;
			default:
				break;
		}
		
		return etVO;
	}
	private List<ExcelFieldVO> getCustomerImportFieldList() {
		List<ExcelFieldVO> fieldList = new ArrayList<ExcelFieldVO>();
		
		ExcelFieldVO field1 = new ExcelFieldVO();
		field1.setFieldName("biztypeId");
		field1.setCellName("客户类型");
		field1.setRequired(true);
		field1.setFieldType(Long.class);
		field1.setDataType(ExcelDataTypeEnum.DOC.getValue());
		field1.setDataRule(new ExcelDataRuleVO("1"));
		
		ExcelFieldVO field2 = new ExcelFieldVO();
		field2.setFieldName("industry");
		field2.setCellName("行业");
		field2.setFieldType(Integer.class);
		field2.setDataType(ExcelDataTypeEnum.REF.getValue());
		field2.setDataRule(new ExcelDataRuleVO("base_industry", "name"));
		
		ExcelFieldVO field3 = new ExcelFieldVO();
		field3.setFieldName("cannelType");
		field3.setCellName("渠道类型");
		field3.setDataType(ExcelDataTypeEnum.REF.getValue());
		field3.setDataRule(new ExcelDataRuleVO("sys_mt_biztype", "name"));
		
		ExcelFieldVO field4 = new ExcelFieldVO();
		field4.setFieldName("code");
		field4.setCellName("客户编码");
		
		ExcelFieldVO field5 = new ExcelFieldVO();
		field5.setFieldName("name");
		field5.setCellName("客户名称");
		field5.setRequired(true);
		
		ExcelFieldVO field6 = new ExcelFieldVO();
		field6.setFieldName("fullname");
		field6.setCellName("客户全称");
		
		ExcelFieldVO field7 = new ExcelFieldVO();
		field7.setFieldName("parentId");
		field7.setCellName("上级客户名称");
		field7.setDataType(ExcelDataTypeEnum.REF.getValue());
		field7.setDataRule(new ExcelDataRuleVO("cum_customer", "name"));
		
		ExcelFieldVO field9 = new ExcelFieldVO();
		field9.setFieldName("turnover");
		field9.setCellName("营业额（万元）");
		field9.setFieldType(BigDecimal.class);
		
		ExcelFieldVO field10 = new ExcelFieldVO();
		field10.setFieldName("buyPotential");
		field10.setCellName("购买潜力(元)");
		field10.setFieldType(BigDecimal.class);
		
		ExcelFieldVO field11 = new ExcelFieldVO();
		field11.setFieldName("employeeNum");
		field11.setCellName("员工人数");
		field11.setFieldType(Long.class);
		
		ExcelFieldVO field12 = new ExcelFieldVO();
		field12.setFieldName("remark");
		field12.setCellName("备注");
		
		ExcelFieldVO field13 = new ExcelFieldVO();
		field13.setFieldName("tel");
		field13.setCellName("电话");
		
		ExcelFieldVO field14 = new ExcelFieldVO();
		field14.setFieldName("email");
		field14.setCellName("邮箱");
		
		ExcelFieldVO field15 = new ExcelFieldVO();
		field15.setFieldName("regCapital");
		field15.setCellName("注册资本");
		
		ExcelFieldVO field16 = new ExcelFieldVO();
		field16.setFieldName("legalRepresent");
		field16.setCellName("法定代表人");
		
		ExcelFieldVO field17 = new ExcelFieldVO();
		field17.setFieldName("creditCode");
		field17.setCellName("统一信用代码");
		
		ExcelFieldVO field18 = new ExcelFieldVO();
		field18.setFieldName("taxpayerNo");
		field18.setCellName("纳税人识别号");

		ExcelFieldVO field19 = new ExcelFieldVO();
		field19.setFieldName("bizRegno");
		field19.setCellName("工商注册号");
		
		ExcelFieldVO field20 = new ExcelFieldVO();
		field20.setFieldName("bizRegno");
		field20.setCellName("组织机构代码");

		fieldList.add(field1);
		fieldList.add(field2);
		fieldList.add(field3);
		fieldList.add(field4);
		fieldList.add(field5);
		fieldList.add(field6);
		fieldList.add(field7);
		fieldList.add(field9);
		fieldList.add(field10);
		fieldList.add(field11);
		fieldList.add(field12);
		fieldList.add(field13);
		fieldList.add(field14);
		fieldList.add(field15);
		fieldList.add(field16);
		fieldList.add(field17);
		fieldList.add(field18);
		fieldList.add(field19);
		fieldList.add(field20);

		return fieldList; 
	}
	
	//导出模板
	private ExcelTemplateVO getExportTemplateVO(Integer type) {
		
		ExcelTemplateVO etVO = new ExcelTemplateVO();

		switch (type.intValue()) {
			case ExcelTemplateConstant.Customer:
				etVO.setFieldList(getCustomerExportFieldList());
				etVO.setEntityName("公司客户");
				etVO.setEntityClass(CustomerVO.class);
				etVO.setService(ICustomerRmService.class);
				etVO.setMethod("getList");
				break;
			case ExcelTemplateConstant.GroupCustomer:
				etVO.setFieldList(getCustomerExportFieldList());
				etVO.setEntityName("集团客户");
				etVO.setEntityClass(CustomerVO.class);
				etVO.setService(IGroupCustomerRmService.class);
				etVO.setMethod("getList");
				break;

			default:
				break;
		}
		return etVO;
	}
	private List<ExcelFieldVO> getCustomerExportFieldList() {
		List<ExcelFieldVO> fieldList = new ArrayList<ExcelFieldVO>();
		
		ExcelFieldVO field1 = new ExcelFieldVO();
		field1.setFieldName("biztypeName");
		field1.setCellName("客户类型");
		
		ExcelFieldVO field2 = new ExcelFieldVO();
		field2.setFieldName("industryName");
		field2.setCellName("行业");
		
		ExcelFieldVO field3 = new ExcelFieldVO();
		field3.setFieldName("cannelTypeName");
		field3.setCellName("渠道类型");
		
		ExcelFieldVO field4 = new ExcelFieldVO();
		field4.setFieldName("code");
		field4.setCellName("客户编码");
		
		ExcelFieldVO field5 = new ExcelFieldVO();
		field5.setFieldName("name");
		field5.setCellName("客户名称");
		field5.setRequired(true);
		
		ExcelFieldVO field6 = new ExcelFieldVO();
		field6.setFieldName("fullname");
		field6.setCellName("客户全称");
		
		ExcelFieldVO field7 = new ExcelFieldVO();
		field7.setFieldName("parentName");
		field7.setCellName("上级客户名称");
		
		ExcelFieldVO field9 = new ExcelFieldVO();
		field9.setFieldName("turnover");
		field9.setCellName("营业额（万元）");
		
		ExcelFieldVO field10 = new ExcelFieldVO();
		field10.setFieldName("buyPotential");
		field10.setCellName("购买潜力(元)");
		
		ExcelFieldVO field11 = new ExcelFieldVO();
		field11.setFieldName("employeeNum");
		field11.setCellName("员工人数");
		
		ExcelFieldVO field12 = new ExcelFieldVO();
		field12.setFieldName("remark");
		field12.setCellName("备注");
		
		ExcelFieldVO field13 = new ExcelFieldVO();
		field13.setFieldName("tel");
		field13.setCellName("电话");
		
		ExcelFieldVO field14 = new ExcelFieldVO();
		field14.setFieldName("email");
		field14.setCellName("邮箱");
		
		ExcelFieldVO field15 = new ExcelFieldVO();
		field15.setFieldName("regCapital");
		field15.setCellName("注册资本");
		
		ExcelFieldVO field16 = new ExcelFieldVO();
		field16.setFieldName("legalRepresent");
		field16.setCellName("法定代表人");
		
		ExcelFieldVO field17 = new ExcelFieldVO();
		field17.setFieldName("creditCode");
		field17.setCellName("统一信用代码");
		
		ExcelFieldVO field18 = new ExcelFieldVO();
		field18.setFieldName("taxpayerNo");
		field18.setCellName("纳税人识别号");

		ExcelFieldVO field19 = new ExcelFieldVO();
		field19.setFieldName("bizRegno");
		field19.setCellName("工商注册号");
		
		ExcelFieldVO field20 = new ExcelFieldVO();
		field20.setFieldName("bizRegno");
		field20.setCellName("组织机构代码");
		
		fieldList.add(field1);
		fieldList.add(field2);
		fieldList.add(field3);
		fieldList.add(field4);
		fieldList.add(field5);
		fieldList.add(field6);
		fieldList.add(field7);
		fieldList.add(field9);
		fieldList.add(field10);
		fieldList.add(field11);
		fieldList.add(field12);
		fieldList.add(field13);
		fieldList.add(field14);
		fieldList.add(field15);
		fieldList.add(field16);
		fieldList.add(field17);
		fieldList.add(field18);
		fieldList.add(field19);
		fieldList.add(field20);
		
		return fieldList;
	}
}
